How-to: Migrating Exact Synergy Enterprise database to Windows SQL Azure
Introduction
Note: This document is only relevant to the controlled release participants. This document explains the steps to migrate the Exact
Synergy Enterprise database to Microsoft SQL Azure.
Prerequisites
You must have the following prerequisites:
- An existing Exact Synergy Enterprise environment with the
database running on the Microsoft SQL Server. Ensure you have the following:
- The
corresponding Exact Synergy Enterprise license for the database.
- The Exact
Synergy Enterprise administrator user to log in to the environment.
- SQL
administrator rights to access the SQL Server.
- Access to the SQL Azure server. Ensure you have the following:
- The
administrator account. For example, the SQL Azure administrator user name and
password.
- Access to the Exact Synergy Enterprise website on the cloud
environment. Ensure you have the following:
- FTP or
portal access if Exact Synergy Enterprise will be running on the Azure website,
or
- Remote
desktop access if Exact Synergy Enterprise will be running on the Azure virtual
machine.
Note:
- It is recommended that you back up the Exact Synergy Enterprise
SQL server database in case any of the following steps encounters errors.
- This document assumes that the Exact Synergy Enterprise database
migration to SQL Azure is for the use of Exact Synergy Enterprise hosted in
Azure using the federated identity authentication.
- During the migration of the database, the appropriate SQL Server Management Studio (SSMS) version must be used. For example, if you are migrating the SQL server 2016 database, SSMS 2016 or higher must be used; likewise if you are migrating the SQL server 2017 database, SSMS 17.x or higher must be used.
Setting up the database
From product update 257 onwards, Exact Synergy Enterprise
provides a function to change the database connection mode from application
role to SQL login and vice versa. This is required because SQL Azure does not
support application roles.
- Log in to the
existing Exact Synergy Enterprise environment with the Exact Synergy Enterprise
administrator user.
Note: Run the browser with the
administrator rights by right-clicking the mouse, and then click Run as
administrator.
- Create or edit
the person with the federated identity (WAAD or Auth0 email address). This is
the account that you will be using for the first time to access the
cloud-hosted Exact Synergy Enterprise.
Note: The existing Exact Synergy
Enterprise must have a person with a WAAD or an Auth0 email address.
- Go to Modules ? System ?
Setup ? Settings – Database ? Connection setting.
- On the Connection
Setting page, select SQL Login at Connection Mode.
- Define the
administrative SQL Server login credentials under the Login section.
Select the Integrated security check box or type the user name and
password.
- Click Save.
If successful, the SQL Login page will be displayed
if you go to Modules ? System ? Setup ?
Settings – Database ? Connection
setting. The page will display the Update database password field. Once the Exact Synergy Enterprise connection database mode is set to "SQL Login", a random SQL login and password will be created for the connection. The credentials will then be encrypted and saved in the db.config file.
In the SQL login connection mode, the administrator can change the SQL password via the Update database password field. This is an optional step. For more information, see Changing database passwords.
The db.config entry for Exact Synergy Enterprise will be
changed to use the SQL login.
If you encounter error(s) in this section, see the Known
issues - Setting up the database section.
Deploying the database to SQL Azure
Once the Exact Synergy Enterprise database is using the SQL
login database connection mode, you can start to deploy the database to SQL
Azure.
- In the SQL
Management Studio screen, right-click on Database, click Tasks,
and then click Deploy Database to Windows Azure SQL Database.
- In the Introduction
screen, click Next.
- In the Deployment
Settings screen, do the following:
- Click Connect.
- In the Connect to Server screen, define your SQL Azure
details, and then click Connect.
- In the Deployment Settings screen, define the database
name at New database name.
- The other recommended settings are:
- Select Standard at Edition of Windows Azure SQL
Database.
- Select 250 at Maximum database size (GB).
- Select S2 at Service Objective.
- Click Next.
- In the last
screen, click Finish.
If you encounter error(s) in this
section, see the Known issues - Setting up the database section.
Setting up Exact Synergy Enterprise to use the database on SQL Azure
This section assumes that Exact Synergy Enterprise is hosted
on the Azure website or on the Azure virtual machine.
- Access the
web.config file for the cloud-hosted Exact Synergy Enterprise and ensure that
HostedEnvironment under <appSettings> is set to “2”.
- Ensure that the
db.config is empty (or cleared for the virtual directory).
- In SQL
Management Studio, connect to the SQL Azure database.
- Ensure there is
a federated identity user (WAAD or Auth0) in the Humres table.
- Open Exact
Synergy Enterprise in a browser.
- Log in with the
federated identity that you have created under the Setting up the database
section.
- In the Database:
Create screen, select the Exact Synergy Enterprise license, and click Continue.
- Select Open
an existing database.
- In the Database:
Open screen, do the following:
- Select SQL Azure at DBMS.
- Type the SQL Azure server name at Server.
- Type the SQL Azure database name at Database.
- Type the SQL Azure administrator user name at Login name and
password at Password.
- Click Open.
You should be able to access Exact Synergy Enterprise with
the database hosted on SQL Azure.
Known issues
Setting up the database
|
Error:
|
|
Invalid: Database -
Failed to connect to server : Error in SQL statement 'DROP SCHEMA Baco' :
Cannot drop schema 'Baco' because it is being referenced by object
'DF__Integrati__Clien__1D5CFB42'.
|
|
Explanation:
|
|
This may occur when you
try to change from AppRole to SQL Login at System à Setup à Settings – Database à Connection settings for older databases that had Exact
Lightweight Integration Server (ELIS) Add On activated, creating
the IntegrationClient table under Baco schema instead of dbo.
|
|
Solution:
|
|
Change the schema
owner of IntegrationClient from Baco to dbo using the following statement:
ALTER SCHEMA dbo TRANSFER Baco.IntegrationServers
|
Deploying the database to SQL Azure
|
Error:
|
|
Error SQL71564:
Error validating element [dbo].[xxxxx]: The element [dbo].[xxxxx] cannot be
deployed as the script body is encrypted.
|
|
Explanation:
|
|
SQL Azure does not support encrypted scripts. These scripts should be re-created without the encryption before trying to migrate.
|
|
Solution:
|
|
Drop the customized
function or procedure.
|
|
Error:
|
|
Could not import
package.
Warning SQL0: A
project which specifies SQL Server 2014 as the target platform may experience
compatibility issues with Microsoft Azure SQL Database v12.
Error SQL72014: .Net
SqlClient Data Provider: Msg 102, Level 15, State 1, Procedure
T_D_DATA_SOURCE_MASTER, Line 97 Incorrect syntax near '@error_number'.
Error SQL72045:
Script execution error. The executed script: ….
|
|
Explanation:
|
|
SQL Azure does not support RAISERROR. Scripts that use this command must be removed or reworked.
|
|
Solution:
|
|
DROP function,
trigger, or procedure, or edit and COMMENT the RAISERROR line.
|
Note: The known issues occur only when you migrate
the non-standard Exact Synergy Enterprise databases. The solutions are only
suggestions of the workarounds, and should be evaluated based on your needs.
Related documents
| Main Category: |
Support Product Know How |
Document Type: |
Online help main |
| Category: |
On-line help files |
Security level: |
All - 0 |
| Sub category: |
Details |
Document ID: |
27.549.510 |
| Assortment: |
Exact Synergy Enterprise
|
Date: |
09-05-2019 |
| Release: |
|
Attachment: |
|
| Disclaimer |